Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I want show CY SALE and PY SALE in same row if I select any month year from the dimension the KPI show should be in same Row. below are the
E.g.
Month_year | CY Sales | LY sale |
Jan-22 | 15 | |
Feb-22 | 25 | |
Mar-22 | 28 | |
Apr-22 | 41 | |
Jan-23 | 10 | |
Feb-23 | 20 | |
Mar-23 | 30 | |
Apr-23 | 40 |
If I select Jan-2023.Result should be below
Month_year | CY Sales | LY sale |
Jan-23 | 10 | 15 |
So how to write the expression for that
Hello,
I am thinking of two different solutions :
1 - The simplest but not the best : You create a Month dimension.
In the script:
Month(Month_year) as Month
You put Month in the straight table and then in the set Analysis measures:
For CY : Sum({<Year={"=$(=Year(today()))"}>} Sales)
For PY : Sum({<Year={"=$(=Year(today())-1)"}>} Sales)
2 - In the script in your calendar table you create several types of calendar, one for CY and one for PY.
It should look something like this:
Month_Year_Data | Calendar_Type | Month_Year |
Jan 2023 | CY | Jan 2023 |
Feb 2023 | CY | Feb 2023 |
Jan 2022 | PY | Jan 2023 |
Feb 2022 | PY | Feb 2023 |
You put Month_Year in the straight table and then in the set Analysis measures:
For CY : Sum({<Calendar_Type={'CY'}>} Sales)
For PY : Sum({<Calendar_Type={'PY'}>} Sales)
I hope that's understandable enough.
Best regards,
Steven
Hello,
I am thinking of two different solutions :
1 - The simplest but not the best : You create a Month dimension.
In the script:
Month(Month_year) as Month
You put Month in the straight table and then in the set Analysis measures:
For CY : Sum({<Year={"=$(=Year(today()))"}>} Sales)
For PY : Sum({<Year={"=$(=Year(today())-1)"}>} Sales)
2 - In the script in your calendar table you create several types of calendar, one for CY and one for PY.
It should look something like this:
Month_Year_Data | Calendar_Type | Month_Year |
Jan 2023 | CY | Jan 2023 |
Feb 2023 | CY | Feb 2023 |
Jan 2022 | PY | Jan 2023 |
Feb 2022 | PY | Feb 2023 |
You put Month_Year in the straight table and then in the set Analysis measures:
For CY : Sum({<Calendar_Type={'CY'}>} Sales)
For PY : Sum({<Calendar_Type={'PY'}>} Sales)
I hope that's understandable enough.
Best regards,
Steven