topic Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion in App Development

rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Fri, 17 Jan 2025 19:01:39 GMT

Hi Team,

PFA of my sample data and QVF, I want to show the rolling 6 months data as default with monthyear dim and if there is no data for the rolling months it has to show zero in the bar chart and if user select any timeframe monthyear dim has to be changed accordingly as per the selected timeframe example week or year or date, All these are achieved.

now the issue is if i add another dim in the chart and disable the include null value , i am not getting the zero value. bar chart has to show allways rolling 6 bars, if no data then it shd be zero and similarly pivot shd show the rolling 4 months.

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Fri, 17 Jan 2025 19:12:41 GMT

@olofmalmstrom i hope you helped lot on this and this another scenario, pls help on this

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Sun, 19 Jan 2025 18:35:15 GMT

@olofmalmstrom waiting for your response or team waiting response for above issue

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

olofmalmstrom — Mon, 20 Jan 2025 08:57:33 GMT

Hi, Why can't you keep the "Include null values" in the second dimension?

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Fri, 24 Jan 2025 13:06:23 GMT

sorry for the late reply @olofmalmstrom , if i keep the include null value then data will show null, now the thing client don't want the null he need it as zero now.

i tried alt in the exp not working, he want to see the rolling 4 months data has to come and then if there is no transaction on that date then it shd be zero , i also tried pervious of your example like adding sum({1}0) but not working , but this sum({1}0) working fine for the bar chart only. with one dim as time frame and multiple exp. if we add multiple dim this sum({1}0) not working in this scenario.

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

olofmalmstrom — Fri, 24 Jan 2025 14:02:36 GMT

Is this what you want ?

In your app I set first dim to exclude null

And in the second Dim I set Include null

Nothing else is changed

/Olof

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Sun, 26 Jan 2025 02:19:53 GMT

yes but in the pivot table scenario , Could you please disable the null in the timeframe dim and enable the null in the other dim in the pivot table chart , it will show rolling rolling 4 months but it will be in null it wont have the zero values, now these null has to be shown as zero in the pivot table for the rolling 4 months data in pivot table chart, this is the issue

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Mon, 27 Jan 2025 07:49:59 GMT

@olofmalmstrom waiting for your respnse

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Wed, 29 Jan 2025 10:05:53 GMT

Hi @olofmalmstrom

Any updates?

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

olofmalmstrom — Thu, 30 Jan 2025 08:54:05 GMT

The Pivot table is special in how it handles this. If you really want this to work as described, you will need to create a Dummy value for each and every possible combination of the dimensions in the pivot, but this can result in a really large fact table so I would talk to the users if it is really necessary. One workaround could perhaps be to create a straight table with 6 measures, one for each month...

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Thu, 30 Jan 2025 13:59:45 GMT

Hi, I totally agree on your point , i tried by generating missing data for the fact , the 21k raw records went to 8.3million rows record, attached my qvf and below are the screen shot. refer the app second sheet.

Note: generating missing data concepts comes in to picture with cartesian join concepts in the qlik script

if we use straight table with 6 measure, then how the rolling logic come in to picture could you pls share your qvf file.

i will try to close this thread after seeing your qvf file with straight table logic, please be in touch with my scenarios in upcoming days with your helping hands

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

olofmalmstrom — Thu, 30 Jan 2025 15:21:32 GMT

Sorry, no qvf to share, just a thought in my head that you could create 6 measures, one that calculates the value for for the max yearmonth, on for max yearmonth-1 etc...

Good luck 🙂

Re: rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

manoranjan_321988 — Fri, 31 Jan 2025 08:12:56 GMT

will try and reply to you asap and close the thread after my try.