none
PowerPivot datumové řezy RRS feed

  • Dotaz

  • Krásné pondělí všem,

    začínám s PowerPivotem a DAX a hned jsem narazil. Mám velkou tabulku s údaji o nemocenských, dovolených atd. Důležité sloupce jsou PID (id osoby), DatumOD, DatumDO. např.:

    PID DatumOD DatumDO
    1 1.2.2012 5.2.2012
    2 25.1.2012 4.3.2012
    3 20.1.2012 10.2.2012

    Report je nutné generovat za vybraný měsíc. Pomocí datových řezů se dostanu k tabulce, ve které jsou llidé, kteří v daném měsíci byli např. nemocní, ale problém je s daty, která přesahují vybraný měsíc. Z příkladu výše, pokud by šlo o únor, by mi mělo vyhít, že 1 byl nemocný 5 dnů, 2 byl nemocný 29 dnů a 3 byl nemocný 10 dnů. Samozřejmě by mělo přijít ještě očištění o víkendy apod.

    Hledám po netu, pročítám manuály, ale pořád tápu. Nevím jak získat datum (měsíc), které si uživatel vybral a k němu spočítat dny - vybrat řádky ve kterých se vyskytuje vybraný měsíc a podle ohraničení spočítat dny ve zvoleném měsíci.

    Děkuji za rady

    Martin

    pondělí 5. března 2012 9:20

Odpovědi

  • Dobrý den,

    díky za odpověď. Nakonec jsem to našel. Logika je stejná jako píšete (jinak ohraničení ani udělat nejde), ale způsob jakým získat měsíc, který si uživatel vybral je popsán zde: http://www.powerpivotblog.nl/powerpivot-dax-case-resouce-planning-report-with-selecting-a-date-parameter

    já jsem si to upravil do následující podoby:

    =IF( countrows(values('datum'[rok])) = 1
    &&
    countrows(values('datum'[mesic])) = 1,
    SUMX(nemocenska,
    IF([DATUM_OD] >= STARTOFMONTH(values('datum'[datum])) && [DATUM_DO] <= ENDOFMONTH(values('datum'[datum])),COUNTROWS(DATESBETWEEN(datum[PracDatum],[DATUM_OD], [DATUM_DO])),
    IF([DATUM_OD] < STARTOFMONTH(values('datum'[datum])) && [DATUM_DO] > ENDOFMONTH(values('datum'[datum])), COUNTROWS(DATESBETWEEN(datum[PracDatum],STARTOFMONTH(values('datum'[datum])),ENDOFMONTH(values('datum'[datum])))),
    IF([DATUM_OD] >= STARTOFMONTH(values('datum'[datum])) && [DATUM_DO] > ENDOFMONTH(values('datum'[datum])) && [DATUM_OD] <= ENDOFMONTH(values('datum'[datum])), COUNTROWS(DATESBETWEEN(datum[PracDatum],[DATUM_OD], ENDOFMONTH(values('datum'[datum])))),
    IF([DATUM_OD] < STARTOFMONTH(values('datum'[datum])) && [DATUM_DO] <= ENDOFMONTH(values('datum'[datum])) &&  [DATUM_DO] >=STARTOFMONTH(values('datum'[datum])), COUNTROWS(DATESBETWEEN(datum[PracDatum],STARTOFMONTH(values('datum'[datum])),[DATUM_DO])),
    BLANK()))))),BLANK())

    na první pohled trochu komplikované, ale čtyři podmínky jsou nutné pro podchycení všech stavů dat, které mohou nastat. Protože jde o měsíční report, uživatelé si mohou vybrat pouze měsíc a rok. Proto STARTOFMONTH a ENDOFMONTH vrací konec nebo začátek měsíce, aby se uživatelům usnadnila práce.

    Víkendy jsem nakonec vyřešil následující tabulkou:

    Vzorcem, podle čísla dne v týdnu, poznám, jestli jde o víkend nebo ne a podle toho vzniká sloupec 'PracDatum'.  Následně pomocí vzorce DATESBETWEEN zjistím sadu datumů mezi DATUM_OD a DATUM_DO (případně od začátku nebo do konce měsíce) v tabulce 'DATUM'[PracDatum] a spočtu její řádky. Protože má sloupec mezery tam, kde je víkend, dostanu se ke správnému výsledku :-) Co jsem popsal má na statosti tento kus kódu, který je pochopitelnější, než samotný popis:

    COUNTROWS(DATESBETWEEN(datum[PracDatum],[DATUM_OD], [DATUM_DO]))

    Funkci NETWORKDAYS bohužel nejde použít, protože není v DAX dostupná. Snad to někomu pomůže
    • Označen jako odpověď Vilca pondělí 19. března 2012 8:13
    pondělí 19. března 2012 8:13

Všechny reakce

  • Dobrý den,

    Očištění o víkendy se dá udělat pomocí funkce NETWORKDAYS, přikládám v excelu.

    https://skydrive.live.com/view.aspx?cid=8A9996BA353478EA&resid=8A9996BA353478EA%21617

    Ohraničení bych řekl že můžete dělat filtrem kde:

    datumDO >= 1.1.2012 AND datumOD <= 31.1.2012

    a pak musíte ještě přidělat pokud je datumOD menší než daný měsíc (v sql příkladu leden 2012), tak datumOD změní na prvního v měsíci a to samé pokud je datumDO větší než daný měsíc tak změnit na posledního v měsící. a pak provést funkci NETWORKDAYS (+ nějaké korigace +-1 kvůli prvnímu dni nemoci).

    S PowerPivotem jsem nikdy nepracoval, nicméně doufám že i tak pomůže co jsem napsal.

    VJ

    • Označen jako odpověď Jiří JanataMember neděle 18. března 2012 20:00
    • Zrušeno označení jako odpověď Vilca pondělí 19. března 2012 8:13
    pátek 9. března 2012 23:01
  • Dobrý den,

    díky za odpověď. Nakonec jsem to našel. Logika je stejná jako píšete (jinak ohraničení ani udělat nejde), ale způsob jakým získat měsíc, který si uživatel vybral je popsán zde: http://www.powerpivotblog.nl/powerpivot-dax-case-resouce-planning-report-with-selecting-a-date-parameter

    já jsem si to upravil do následující podoby:

    =IF( countrows(values('datum'[rok])) = 1
    &&
    countrows(values('datum'[mesic])) = 1,
    SUMX(nemocenska,
    IF([DATUM_OD] >= STARTOFMONTH(values('datum'[datum])) && [DATUM_DO] <= ENDOFMONTH(values('datum'[datum])),COUNTROWS(DATESBETWEEN(datum[PracDatum],[DATUM_OD], [DATUM_DO])),
    IF([DATUM_OD] < STARTOFMONTH(values('datum'[datum])) && [DATUM_DO] > ENDOFMONTH(values('datum'[datum])), COUNTROWS(DATESBETWEEN(datum[PracDatum],STARTOFMONTH(values('datum'[datum])),ENDOFMONTH(values('datum'[datum])))),
    IF([DATUM_OD] >= STARTOFMONTH(values('datum'[datum])) && [DATUM_DO] > ENDOFMONTH(values('datum'[datum])) && [DATUM_OD] <= ENDOFMONTH(values('datum'[datum])), COUNTROWS(DATESBETWEEN(datum[PracDatum],[DATUM_OD], ENDOFMONTH(values('datum'[datum])))),
    IF([DATUM_OD] < STARTOFMONTH(values('datum'[datum])) && [DATUM_DO] <= ENDOFMONTH(values('datum'[datum])) &&  [DATUM_DO] >=STARTOFMONTH(values('datum'[datum])), COUNTROWS(DATESBETWEEN(datum[PracDatum],STARTOFMONTH(values('datum'[datum])),[DATUM_DO])),
    BLANK()))))),BLANK())

    na první pohled trochu komplikované, ale čtyři podmínky jsou nutné pro podchycení všech stavů dat, které mohou nastat. Protože jde o měsíční report, uživatelé si mohou vybrat pouze měsíc a rok. Proto STARTOFMONTH a ENDOFMONTH vrací konec nebo začátek měsíce, aby se uživatelům usnadnila práce.

    Víkendy jsem nakonec vyřešil následující tabulkou:

    Vzorcem, podle čísla dne v týdnu, poznám, jestli jde o víkend nebo ne a podle toho vzniká sloupec 'PracDatum'.  Následně pomocí vzorce DATESBETWEEN zjistím sadu datumů mezi DATUM_OD a DATUM_DO (případně od začátku nebo do konce měsíce) v tabulce 'DATUM'[PracDatum] a spočtu její řádky. Protože má sloupec mezery tam, kde je víkend, dostanu se ke správnému výsledku :-) Co jsem popsal má na statosti tento kus kódu, který je pochopitelnější, než samotný popis:

    COUNTROWS(DATESBETWEEN(datum[PracDatum],[DATUM_OD], [DATUM_DO]))

    Funkci NETWORKDAYS bohužel nejde použít, protože není v DAX dostupná. Snad to někomu pomůže
    • Označen jako odpověď Vilca pondělí 19. března 2012 8:13
    pondělí 19. března 2012 8:13